// tableSubsets.js
// 定义数据集的拆分子集合
class SubsetsTable {
    constructor(app, db) {
        this.app = app;
        this.db = db;
    }

    init() {
        // 创建subsets表结构，记录拆分的训练集、验证集、测试集
        // 为了方便，将所有的原始目录也保存在这个表中，特别记录一个特别类型dir
        // subset_type = directory|train|val|test
        const sqlTable = `
        CREATE TABLE IF NOT EXISTS subsets (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            dataset_id INTEGER NOT NULL,
            subset_type TEXT NOT NULL,
            image_dir TEXT NOT NULL,
            label_dir TEXT DEFAULT '',
            label_list_path TEXT DEFAULT '',
            create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            update_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        );`;

        this.db.run(sqlTable, (err) => {
            if (err) {
                console.error('Create "subsets" Table Failed, Error: ', err);
            }
        });
    }

    async insert(subsetInfo) {
        return new Promise((resolve, reject) => {
            const { dataset_id, image_dir, label_dir, subset_type, label_list_path } = subsetInfo;

            const sqlInsert = `
                INSERT INTO subsets (dataset_id, image_dir, label_dir, subset_type, label_list_path, create_time, update_time)
                VALUES (?, ?, ?, ?, ?, datetime(CURRENT_TIMESTAMP, '+8 hour') , datetime(CURRENT_TIMESTAMP, '+8 hour'));`;
            this.db.run(sqlInsert, [dataset_id, image_dir, label_dir, subset_type, label_list_path], function (err) {
                if (err) {
                    reject(err);
                } else {
                    resolve(dataset_id);
                }
            });
        });
    }

    // 更新数据集subsets信息
    async update(subsetInfo) {
        // console.log(`update subsetInfo = `, subsetInfo);
        return new Promise(async (resolve, reject) => {
            let sqlUpdateSQL = 'UPDATE subsets SET';
            const params = [];

            // update image_dir if image_dir property exitsted
            if (subsetInfo.image_dir !== null && subsetInfo.image_dir !== undefined) {
                sqlUpdateSQL += ' image_dir = ?,';
                params.push(subsetInfo.image_dir);
            }

            // update label_dir if label_dir property exitsted
            if (subsetInfo.label_dir !== null && subsetInfo.label_dir !== undefined) {
                sqlUpdateSQL += ' label_dir = ?,';
                params.push(subsetInfo.label_dir);
            }

            // update subset_type if subset_type property exitsted
            if (subsetInfo.subset_type !== null && subsetInfo.subset_type !== undefined) {
                sqlUpdateSQL += ' subset_type = ?,';
                params.push(subsetInfo.subset_type);
            }

            // update label_list_path if label_list_path property exitsted
            if (subsetInfo.label_list_path !== null && subsetInfo.label_list_path !== undefined) {
                sqlUpdateSQL += ' label_list_path = ?,';
                params.push(subsetInfo.label_list_path);
            }

            // Remove the trailing comma and add the WHERE clause
            sqlUpdateSQL += " update_time = datetime(CURRENT_TIMESTAMP, '+8 hour') WHERE id = ?;";
            params.push(subsetInfo.id);

            // Execute the UPDATE SQL statement
            this.db.run(sqlUpdateSQL, params, (err) => {
                if (err) {
                    console.error('Error updating subsets:', err);
                    reject({ code: -1, msg: err.message });
                }

                resolve({ code: 0, msg: 'Updated OK' });
            });
        });
    }

    async delete(subsetId) {
        const deleteStatement = `
            DELETE FROM subsets WHERE id = ?;
        `;

        return new Promise((resolve, reject) => {
            this.db.run(deleteStatement, subsetId, (err) => {
                if (err) {
                    reject(err.message);
                } else {
                    resolve(true);
                }
            });
        });
    }
    async get(subset_id) {
        return new Promise((resolve, reject) => {
            const sqlQuery = `SELECT * FROM subsets${subset_id ? ' WHERE id = ?' : ''};`;
            const params = subset_id ? [subset_id] : [];

            this.db.all(sqlQuery, params, (err, rows) => {
                if (err) {
                    reject(err);
                } else {
                    resolve(rows);
                }
            });
        });
    }
}

module.exports = { SubsetsTable };
